---
title: Patching & hotfixing
metaDescription: How to reconcile the migration history after applying a hotfix or patch to a production environment.
---

<TopBlock>

Patching or hotfixing a database involves making an often time critical change directly in production. For example, you might add an index directly to a production database to resolve an issue with a slow-running query.

Patching the production database directly results in **schema drift**: your database schema has 'drifted away' from the source of truth, and is out of sync with your migration history. You can use the `prisma migrate resolve` command to reconcile your migration history _without_ having to remove and re-apply the hotfix with `prisma migrate deploy`.

:::warning

This guide **does not apply for MongoDB**.<br />
Instead of `migrate dev`, [`db push`](/orm/prisma-migrate/workflows/prototyping-your-schema) is used for [MongoDB](/orm/overview/databases/mongodb).

:::

</TopBlock>

## Reconciling your migration history with a patch or hotfix

The following scenario assumes that you made a manual change in production and want to propagate that change to your migration history and other databases.

To reconcile your migration history and database schema in production:



1. Replicate the change you made in production in the schema - for example, add an `@@index` to a particular model.
1. Generate a new migration and take note of the full migration name, including a timestamp, which is written to the CLI:(`20210316150542_retroactively_add_index`):

   <CodeWithResult expanded={true}>
   <cmd>

   ```terminal
   npx prisma migrate dev --name retroactively-add-index
   ```

   </cmd>
   <cmdResult>

    ```bash no-copy
    migrations/
    └─ 20210316150542_retroactively_add_index/
    └─ migration.sql

    Your database is now in sync with your schema.

    ✔ Generated Prisma Client (2.19.0-dev.29) to .\node_modules\@prisma\client in 190ms
    ```

   </cmdResult>
   </CodeWithResult>

1. Push the migration to production **without running `migrate deploy`**. Instead, mark the migration created in the previous step as 'already applied' so that Prisma Migrate does not attempt to apply your hotfix a second time:

   ```terminal
   npx prisma migrate resolve --applied "20201127134938-retroactively-add-index"
   ```

   This command adds the migration to the migration history table without running the actual SQL.

1. Repeat the previous step for other databases that were patched - for example, if you applied the patch to a staging database.

1. Propagate the migration to other databases that were not patched - for example, by committing the migration to source control and allowing your CI/CD pipeline to apply it to all databases.



> **Note**: The migration will not be applied to databases where it has been marked as already applied by the `prisma migrate resolve` command.

## Failed migration

A migration might fail if:

- You [modify a migration before running it](/orm/prisma-migrate/workflows/customizing-migrations) and introduce a syntax error
- You add a mandatory (`NOT NULL`) column to a table that already has data
- The migration process stopped unexpectedly
- The database shut down in the middle of the migration process

Each migration in the `_prisma_migrations` table has a `logs` column that stores the error.

There are two ways to deal with failed migrations in a production environment:

- Roll back, optionally fix issues, and re-deploy
- Manually complete the migration steps and resolve the migration

### Option 1: Mark the migration as rolled back and re-deploy

The following example demonstrates how to roll back a migration, optionally make changes to fix the issue, and re-deploy:

1. Mark the migration as rolled back - this updates the migration record in the `_prisma_migrations` table to register it as rolled back, allowing it to be applied again:

   ```terminal
   npx prisma migrate resolve --rolled-back "20201127134938_added_bio_index"
   ```

1. If the migration was partially run, you can either:

   - Modify the migration to check if a step was already completed (for example: `CREATE TABLE ... IF NOT EXISTS`) _OR_
   - Manually revert the steps that were completed (for example, delete created tables)

   > If you modify the migration, make sure you copy it back to source control to ensure that state of your production database is reflected exactly in development.

1. Fix the root cause of the failed migration, if relevant - for example, if the migration failed due to an issue with the SQL script itself. Make sure that you copy any changed migrations back to source control.

1. Re-deploy the migration:

   ```terminal
   npx prisma migrate deploy
   ```

### Option 2: Manually complete migration and resolve as applied

The following example demonstrates how to manually complete the steps of a migration and mark that migration as applied.

1. Manually complete the migration steps on the production database. Make sure that any manual steps exactly match the steps in the migration file, and copy any changes back to source control.

1. Resolve the migration as applied - this tells Prisma Migrate to consider the migration successfully applied:

   ```terminal
   npx prisma migrate resolve --applied "20201127134938_my_migration"
   ```

## Fixing failed migrations with `migrate diff` and `db execute`

To help with fixing a failed migration, Prisma ORM provides the following commands for creating and executing a migration file:

- [`prisma migrate diff`](/orm/reference/prisma-cli-reference#migrate-diff) which diffs two database schema sources to create a migration taking one to the state of the second. You can output either a summary of the difference or a sql script. The script can be output into a file via `> file_name.sql` or be piped to the `db execute --stdin` command.
- [`prisma db execute`](/orm/reference/prisma-cli-reference#db-execute) which applies a SQL script to the database without interacting with the Prisma migrations table.

These commands are available in Preview in versions `3.9.0` and later (with the `--preview-feature` CLI flag), and generally available in versions `3.13.0` and later.

This section gives an example scenario of a failed migration, and explains how to use `migrate diff` and `db execute` to fix it.

### Example of a failed migration

Imagine that you have the following `User` model in your schema, in both your local development environment and your production environment:

```prisma file=schema.prisma showLineNumbers
model User {
  id   Int    @id
  name String
}
```

At this point, your schemas are in sync, but the data in the two environments is different.

You then decide to make changes to your data model, adding another `Post` model and making the `name` field on `User` unique:

```prisma file=schema.prisma showLineNumbers
model User {
  id    Int     @id
  name  String  @unique
  email String?
}

model Post {
  id    Int    @id
  title String
}
```

You create a migration called 'Unique' with the command `npx prisma migrate dev -n Unique` which is saved in your local migrations history. Applying the migration succeeds in your dev environment and now it is time to release to production.

Unfortunately this migration can only be partially executed. Creating the `Post` model and adding the `email` column succeeds, but making the `name` field unique fails with the following error:

```bash
ERROR 1062 (23000): Duplicate entry 'paul' for key 'User_name_key'
```

This is because there is non-unique data in your production database (e.g. two users with the same name).

You now need to recover manually from the partially executed migration. Until you recover from the failed state, further migrations using `prisma migrate deploy` are impossible.

At this point there are two options, depending on what you decide to do with the non-unique data:

- You realize that non-unique data is valid and you cannot move forward with your current development work. You want to roll back the complete migration. To do this, see [Moving backwards and reverting all changes](#moving-backwards-and-reverting-all-changes)
- The existence of non-unique data in your database is unintentional and you want to fix that. After fixing, you want to go ahead with the rest of the migration. To do this, see [Moving forwards and applying missing changes](#moving-forwards-and-applying-missing-changes)

#### Moving backwards and reverting all changes

In this case, you need to create a migration that takes your production database to the state of your data model before the last migration.

- First you need your migration history at the time before the failed migration. You can either get this from your git history, or locally delete the folder of the last failed migration in your migration history.
- You now want to take your production environment from its current failed state back to the state specified in your local migrations history:

  - Run the following `prisma migrate diff` command:

    ```terminal wrap
     npx prisma migrate diff \
      --from-url "$DATABASE_URL_PROD" \
      --to-migrations ./prisma/migrations \
      --shadow-database-url $SHADOW_DATABASE_URL \
      --script > backward.sql
    ```

    This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined by your migrations history.
    Note that because we're using `--to-migrations`, the command requires a [shadow database](/orm/prisma-migrate/understanding-prisma-migrate/shadow-database).

  - Run the following `prisma db execute` command:

    ```bash
     npx prisma db execute --url "$DATABASE_URL_PROD" --file backward.sql
    ```

    This applies the changes in the SQL script against the target database without interacting with the migrations table.

  - Run the following `prisma migrate resolve` command:

    ```bash
     npx prisma migrate resolve --rolled-back Unique
    ```

    This will mark the failed migration called 'Unique' in the migrations table on your production environment as rolled back.

Your local migration history now yields the same result as the state your production database is in. You can now modify the datamodel again to create a migration that suits your new understanding of the feature you're working on (with non-unique names).

#### Moving forwards and applying missing changes

In this case, you need to fix the non-unique data and then go ahead with the rest of the migration as planned:

- The error message from trying to deploy the migration to production already told you there was duplicate data in the column `name`. You need to either alter or delete the offending rows.
- Continue applying the rest of the failed migration to get to the data model defined in your `schema.prisma` file:

  - Run the following `prisma migrate diff` command:

    ```bash

    npx prisma migrate diff --from-url "$DATABASE_URL_PROD" --to-schema schema.prisma --script > forward.sql

    ```

    This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined in your `schema.prisma` file.

  - Run the following `prisma db execute` command:

    ```bash
    npx prisma db execute --url "$DATABASE_URL_PROD" --file forward.sql
    ```

    This applies the changes in the SQL script against the target database without interacting with the migrations table.

  - Run the following `prisma migrate resolve` command:

    ```bash
    npx prisma migrate resolve --applied Unique
    ```

    This will mark the failed migration called 'Unique' in the migrations table on your production environment as applied.

Your local migration history now yields the same result as the state your production environment is in. You can now continue using the already known `migrate dev` /`migrate deploy` workflow.

## Migration history conflicts

:::info

This does not apply from version [3.12.0](https://github.com/prisma/prisma/releases/tag/3.12.0) upwards.

:::

`prisma migrate deploy` issues a warning if an already applied migration has been edited - however, it does not stop the migration process. To remove the warnings, restore the original migration from source control.

## Prisma Migrate and PgBouncer

You might see the following error if you attempt to run Prisma Migrate commands in an environment that uses PgBouncer for connection pooling:

```bash
Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists
```

See [Prisma Migrate and PgBouncer workaround](/orm/prisma-client/setup-and-configuration/databases-connections/pgbouncer) for further information and a workaround. Follow [GitHub issue #6485](https://github.com/prisma/prisma/issues/6485) for updates.
